﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class EmployeeManager:ConnectionManager
    {
        #region Add/Update
        public SqlException AddEmployee(EmployeeUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO M_EMPLOYEE (LastName, FirstName, MiddleName, ExtensionName, EmpPosition, EmpDepartment, EmpHead, EmpStatus) VALUES (@LastName, @FirstName, @MiddleName, @ExtensionName, @EmpPosition, @EmpDepartment, @EmpHead, @EmpStatus)";

                oCommand.Parameters.Add(new SqlParameter("@LastName", oUnit.LastName));
                oCommand.Parameters.Add(new SqlParameter("@FirstName", oUnit.FirstName));
                oCommand.Parameters.Add(new SqlParameter("@MiddleName", oUnit.MiddleName));
                oCommand.Parameters.Add(new SqlParameter("@ExtensionName", oUnit.Extension));

                oCommand.Parameters.Add(new SqlParameter("@EmpPosition", oUnit.Position));
                oCommand.Parameters.Add(new SqlParameter("@EmpDepartment", oUnit.Department));
                oCommand.Parameters.Add(new SqlParameter("@EmpHead", oUnit.Superior));
                oCommand.Parameters.Add(new SqlParameter("@EmpStatus", oUnit.Status));


                oCommand.ExecuteNonQuery();

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        public SqlException UpdateEmployee(EmployeeUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE M_EMPLOYEE SET LastName=@LastName, FirstName=@FirstName, MiddleName=@MiddleName, ExtensionName=@ExtensionName, EmpPosition=@EmpPosition, EmpDepartment=@EmpDepartment, EmpHead=@EmpHead, EmpStatus=@EmpStatus WHERE EmpId=@EmpId";
                oCommand.Parameters.Add(new SqlParameter("@EmpId", oUnit.EmployeeID));
                oCommand.Parameters.Add(new SqlParameter("@LastName", oUnit.LastName));
                oCommand.Parameters.Add(new SqlParameter("@FirstName", oUnit.FirstName));
                oCommand.Parameters.Add(new SqlParameter("@MiddleName", oUnit.MiddleName));
                oCommand.Parameters.Add(new SqlParameter("@ExtensionName", oUnit.Extension));

                oCommand.Parameters.Add(new SqlParameter("@EmpPosition", oUnit.Position));
                oCommand.Parameters.Add(new SqlParameter("@EmpDepartment", oUnit.Department));
                oCommand.Parameters.Add(new SqlParameter("@EmpHead", oUnit.Superior));
                oCommand.Parameters.Add(new SqlParameter("@EmpStatus", oUnit.Status));

                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }
        #endregion

        #region Others

        public SqlException ChangeStatusToResigned(string _EMPID)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;

                oCommand.CommandText = "UPDATE M_EMPLOYEE SET EmpStatus='Resigned' WHERE EmpId=@EmpID";
                oCommand.Parameters.Add(new SqlParameter("@EmpId", _EMPID));
                oCommand.ExecuteNonQuery();
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }
        }

        #endregion


        #region Get Data

        public DataTable GetEmployeesByDepartment(string _DEPARTMENT)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_ACTIVE_EMPLOYEES WHERE Department=@EmpDepartment";
            oCommand.Parameters.Add(new SqlParameter("@EmpDepartment", _DEPARTMENT));

            DataSet ds = new DataSet();
            SqlDataAdapter oAdapte = new SqlDataAdapter();
            oAdapte.SelectCommand = oCommand;
            oAdapte.Fill(ds);

            return ds.Tables[0];

        }


        public DataTable GetEmployeesByFullName(string _Name)
        {
            SqlCommand oCommand = new SqlCommand();
            _Name = '%' + _Name + '%';
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM VW_ACTIVE_EMPLOYEES WHERE FullName LIKE @FullName";
            oCommand.Parameters.Add(new SqlParameter("@FullName", _Name));

            DataSet ds = new DataSet();
            SqlDataAdapter oAdapte = new SqlDataAdapter();
            oAdapte.SelectCommand = oCommand;
            oAdapte.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetEmployeeInfo(string sEmpID)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM M_EMPLOYEE WHERE EmpID=@EmpID";
            oCommand.Parameters.Add(new SqlParameter("@EmpID", sEmpID));

            DataSet ds = new DataSet();
            SqlDataAdapter oAdapte = new SqlDataAdapter();
            oAdapte.SelectCommand = oCommand;
            oAdapte.Fill(ds);

            return ds.Tables[0];

        }

        #endregion
    }
}
